-- Tags: long, no-random-settings

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (key UInt32, key1 UInt32, key2 Nullable(UInt32), key3 UInt32, s String) engine = MergeTree ORDER BY tuple();
CREATE TABLE t2 (key UInt32, key1 UInt32, key2 UInt32, key3 Nullable(UInt32), s String) engine = MergeTree ORDER BY tuple();

{% set table_size = 15 %}

INSERT INTO t1
    SELECT
        sipHash64(number, 't1') % {{ table_size }} + 1 as key,
        sipHash64(number, 'x') % 2 + 1 as key1,
        if(number % 5 == 0, NULL, sipHash64(number, 'y') % 2 + 1) as key2,
        sipHash64(number, 'z') % 2 + 1 as key3,
        'val' || toString(number) as s
    FROM numbers_mt({{ table_size }});

INSERT INTO t2
    SELECT
        sipHash64(number, 't2') % {{ table_size }} + 1 as key,
        sipHash64(number, 'a') % 2 + 1 as key1,
        sipHash64(number, 'b') % 2 + 1 as key2,
        if(number % 3 == 0, NULL, sipHash64(number, 'c') % 2 + 1) as key3,
        'val' || toString(number) as s
    FROM numbers_mt({{ table_size - 3 }});

{% macro is_implemented(join_algorithm) -%}
{% endmacro -%}

{% for join_algorithm in ['default', 'full_sorting_merge', 'grace_hash'] -%}

SET max_bytes_in_join = '{% if join_algorithm == 'grace_hash' %}10K{% else %}0{% endif %}';

SELECT '-- {{ join_algorithm }} --';
SET join_algorithm = '{{ join_algorithm }}';

{% for block_size in range(1, table_size + 1, 4) -%}
{% for kind in ['ALL', 'ANY'] -%}

SET max_block_size = {{ block_size }};

SELECT '{{ kind }} INNER USING | bs = {{ block_size }}';
SELECT key, empty(t1.s), empty(t2.s) FROM t1
{{ kind }} INNER JOIN t2
USING (key)
ORDER BY t1.key, t2.key
;

SELECT '{{ kind }} INNER | bs = {{ block_size }}';
SELECT t1.key, t2.key, empty(t1.s), empty(t2.s) FROM t1
{{ kind }} INNER JOIN t2
ON t1.key == t2.key
ORDER BY t1.key, t2.key
;

SELECT '{{ kind }} LEFT | bs = {{ block_size }}';
SELECT t1.key, t2.key, t1.s, empty(t2.s) FROM t1
{{ kind }} LEFT JOIN t2
ON t1.key == t2.key
ORDER BY t1.key, t2.key, t1.s
;

SELECT '{{ kind }} RIGHT | bs = {{ block_size }}';
SELECT t1.key, t2.key, empty(t1.s), t2.s FROM t1
{{ kind }} RIGHT JOIN t2
ON t1.key == t2.key
ORDER BY t1.key, t2.key, t2.s
; {{ is_implemented(join_algorithm) }}

SELECT '{{ kind }} INNER | bs = {{ block_size }} | copmosite key';
SELECT t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, empty(t1.s), empty(t2.s) FROM t1
{{ kind }} INNER JOIN t2
ON t1.key1 == t2.key1 AND t1.key2 == t2.key2 AND t1.key3 == t2.key3 AND t1.key1 == t2.key3
ORDER BY t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3
;

SELECT '{{ kind }} LEFT | bs = {{ block_size }} | copmosite key';
SELECT t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, t1.s, empty(t2.s) FROM t1
{{ kind }} LEFT JOIN t2
ON t1.key1 == t2.key1 AND t1.key2 == t2.key2 AND t1.key3 == t2.key3 AND t1.key1 == t2.key3
ORDER BY t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, t1.s
;

SELECT '{{ kind }} RIGHT | bs = {{ block_size }} | copmosite key';
SELECT t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, empty(t1.s), t2.s FROM t1
{{ kind }} RIGHT JOIN t2
ON t1.key1 == t2.key1 AND t1.key2 == t2.key2 AND t1.key3 == t2.key3 AND t1.key1 == t2.key3
ORDER BY t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, t2.s
; {{ is_implemented(join_algorithm) }}

{% endfor -%}
{% endfor -%}

{% for kind in ['ALL', 'ANY'] -%}

SET join_use_nulls = 1;

SELECT '{{ kind }} INNER | join_use_nulls = 1';
SELECT t1.key, t2.key, isNull(t1.s), isNull(t2.s) FROM t1
{{ kind }} INNER JOIN t2
ON t1.key == t2.key
ORDER BY t1.key, t2.key
;

SELECT '{{ kind }} LEFT | join_use_nulls = 1';
SELECT t1.key, t2.key, t1.s, isNull(t2.s) FROM t1
{{ kind }} LEFT JOIN t2
ON t1.key == t2.key
ORDER BY t1.key, t2.key, t1.s
;

SELECT '{{ kind }} RIGHT | join_use_nulls = 1';
SELECT t1.key, t2.key, isNull(t1.s), t2.s FROM t1
{{ kind }} RIGHT JOIN t2
ON t1.key == t2.key
ORDER BY t1.key, t2.key, t2.s
; {{ is_implemented(join_algorithm) }}

SELECT '{{ kind }} INNER | join_use_nulls = 1 | copmosite key';
SELECT t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, empty(t1.s), empty(t2.s) FROM t1
{{ kind }} INNER JOIN t2
ON t1.key1 == t2.key1 AND t1.key2 == t2.key2 AND t1.key3 == t2.key3 AND t1.key1 == t2.key3
ORDER BY t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3
;

SELECT '{{ kind }} LEFT | join_use_nulls = 1 | copmosite key';
SELECT t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, t1.s, empty(t2.s) FROM t1
{{ kind }} LEFT JOIN t2
ON t1.key1 == t2.key1 AND t1.key2 == t2.key2 AND t1.key3 == t2.key3 AND t1.key1 == t2.key3
ORDER BY t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, t1.s
;

SELECT '{{ kind }} RIGHT | join_use_nulls = 1 | copmosite key';
SELECT t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, empty(t1.s), t2.s FROM t1
{{ kind }} RIGHT JOIN t2
ON t1.key1 == t2.key1 AND t1.key2 == t2.key2 AND t1.key3 == t2.key3 AND t1.key1 == t2.key3
ORDER BY t1.key1, t1.key2, t1.key3, t2.key1, t2.key2, t2.key3, t2.s
; {{ is_implemented(join_algorithm) }}

SET join_use_nulls = 0;
SET max_bytes_in_join = 0;

{% endfor -%}
{% endfor -%}

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
